import random
from datetime import datetime, timedelta

# Constants
NUM_STORES = 5
NUM_USERS = 100
NUM_CAR_MODELS = 20
NUM_CARS = 200
NUM_RESERVATIONS = 500
NUM_STORE_ADMINS = NUM_STORES  # One admin per store
NUM_SYSTEM_ADMINS = 10

store_locations = ["New York", "Los Angeles", "Chicago", "Houston", "Phoenix"]
conditions = ["良好", "维修中"]
statuses = ["可租", "已租", "不可租"]
reservation_statuses = ["已还", "未借出", "借出", "逾期", "作废"]
renewal_permissions = ["是", "否"]

# Car model dictionary based on brand and type
car_model_dict = {
    "Toyota": {
        "Truck": "羚羊",
        "Sedan": "普锐斯",
        "SUV": "汉兰达",
        "Coupe": "86",
        "Convertible": "苏拉"
    },
    "Ford": {
        "Truck": "F-150",
        "Sedan": "Fusion",
        "SUV": "Explorer",
        "Coupe": "Mustang",
        "Convertible": "Mustang Convertible"
    },
    "Chevrolet": {
        "Truck": "Silverado",
        "Sedan": "Malibu",
        "SUV": "Tahoe",
        "Coupe": "Camaro",
        "Convertible": "Camaro Convertible"
    },
    "Honda": {
        "Truck": "Ridgeline",
        "Sedan": "Accord",
        "SUV": "CR-V",
        "Coupe": "Civic Coupe",
        "Convertible": "S2000"
    },
    "BMW": {
        "Truck": "X5",
        "Sedan": "3 Series",
        "SUV": "X3",
        "Coupe": "4 Series",
        "Convertible": "Z4"
    }
}

create_event_sql = """
CREATE DEFINER=`root`@`localhost` EVENT `UpdateReservationStatus`
    ON SCHEDULE
        EVERY 1 DAY STARTS (SELECT DATE_ADD(NOW(), INTERVAL 1 DAY))  -- Start tomorrow
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
    -- Update logic as provided...
    UPDATE Reservation
    SET ReservationStatus = '作废'
    WHERE ReservationStatus = '未借出' AND ExpectedCheckoutTime < CURRENT_DATE;

    UPDATE Reservation
    SET ReservationStatus = '逾期'
    WHERE ReservationStatus = '借出' AND ExpectedReturnTime < CURRENT_DATE;

    UPDATE Car
    SET CarStatus = '已租'
    WHERE CarID IN (
        SELECT DISTINCT CarID
        FROM ActualOrder AO
        JOIN Reservation R ON AO.ReservationOrderID = R.ReservationID
        WHERE R.ReservationStatus IN ('借出', '逾期')
    );
END;
"""

def generate_refined_reservation_number(user_id, checkout_time):
    """Generate a refined reservation number with microsecond precision."""
    formatted_checkout_time = checkout_time.strftime("%Y%m%d%H%M%S%f")  # Format: YYYYMMDDHHMMSSffffff
    # In case of extremely unlikely microsecond collision, append a simple counter
    counter = ''.join(random.choices('0123456789', k=3))  # 3-digit random number for extra uniqueness
    return f"RES-{user_id}-{formatted_checkout_time}{counter}"

# Helper functions
def random_date(start, end):
    return start + timedelta(days=random.randint(0, (end - start).days))

def generate_random_string(length=8):
    return ''.join(random.choices('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', k=length))

def update_unrented_to_invalid():
    update_query = """
    UPDATE Reservation
    SET ReservationStatus = '作废'
    WHERE ReservationStatus = '未借出' AND ExpectedCheckoutTime < CURRENT_DATE;
    """
    try:
        cursor.execute(update_query)
        db.commit()
        print("Unrented reservations updated to '作废'")
    except Exception as e:
        db.rollback()
        print(f"Error updating unrented reservations: {e}")

def update_rented_to_overdue():
    update_query = """
    UPDATE Reservation
    SET ReservationStatus = '逾期'
    WHERE ReservationStatus = '借出' AND ExpectedReturnTime < CURRENT_DATE;
    """
    try:
        cursor.execute(update_query)
        db.commit()
        print("Rented reservations updated to '逾期'")
    except Exception as e:
        db.rollback()
        print(f"Error updating rented reservations: {e}")
def update_not_begin():
    update_query = """
    UPDATE Reservation
    SET ReservationStatus = '未借出'
    WHERE ( ReservationStatus = '借出' OR ReservationStatus = '逾期' OR ReservationStatus = '已还') AND ExpectedCheckoutTime > CURRENT_DATE AND ExpectedReturnTime > CURRENT_DATE;
    """
    try:
        cursor.execute(update_query)
        db.commit()
        print("Rented reservations updated to '未借出'")
    except Exception as e:
        db.rollback()
        print(f"Error updating rented reservations: {e}")
def update_car_status_to_rented():
    update_query = """
    UPDATE Car
    SET CarStatus = '已租'
    WHERE CarID IN (
        SELECT DISTINCT CarID
        FROM ActualOrder AO
        JOIN Reservation R ON AO.ReservationOrderID = R.ReservationID
        WHERE R.ReservationStatus IN ('借出', '逾期')
    );
    """
    try:
        cursor.execute(update_query)
        db.commit()
        print("Car statuses updated to '已租' for rented and overdue reservations.")
    except Exception as e:
        db.rollback()
        print(f"Error updating car statuses: {e}")

# Generate data
stores = []
users = []
car_models = []
cars = []
reservations = []
actual_orders = []  # New list for actual orders
store_admins = []
system_admins = []

# Generate Stores
for i in range(NUM_STORES-1):
    store_id = generate_random_string()
    store_admin_id = generate_random_string()
    stores.append((store_id, random.choice(store_locations), store_admin_id, f"Store {i+1}"))
    store_admins.append((store_admin_id, generate_random_string()))
store_id = generate_random_string()
store_admin_id = generate_random_string()
stores.append((store_id, random.choice(store_locations), "SA001", f"Store {NUM_STORES}"))
store_admins.append(("SA001","admin123"))
# Generate Users
for i in range(NUM_USERS):
    users.append((generate_random_string(), generate_random_string(), generate_random_string(), f"Address {i+1}", f"555-{random.randint(1000,9999)}", random_date(datetime(2020, 1, 1), datetime(2024, 1, 1))))
users.append(("xwx","xwx","xwx",f"Address {i+1}", f"555-{random.randint(1000,9999)}", random_date(datetime(2020, 1, 1), datetime(2024, 1, 1))))
# Generate CarModels
for _ in range(NUM_CAR_MODELS):
    brand = random.choice(list(car_model_dict.keys()))
    car_type = random.choice(list(car_model_dict[brand].keys()))
    car_model_name = car_model_dict[brand][car_type]
    car_model_id = f"{brand} {car_model_name}"
    car_models.append((generate_random_string(), car_type, car_model_id, round(random.uniform(20000, 100000), 2)))

# Generate Cars
for i in range(NUM_CARS):
    cars.append((generate_random_string(), random.choice(car_models)[0], random_date(datetime(2015, 1, 1), datetime(2023, 1, 1)), f"XYZ-{random.randint(1000,9999)}", random.choice(conditions), random.choice(statuses), random.choice(stores)[0]))

# Generate Reservations
for _ in range(NUM_RESERVATIONS):
    user_id = random.choice(users)[2]
    car_model_id = random.choice(car_models)[0]
    checkout_store_id = random.choice(stores)[0]
    return_store_id = random.choice(stores)[0]
    checkout_time = random_date(datetime(2023, 1, 1), datetime(2024, 12, 31))
    return_time = checkout_time + timedelta(days=random.randint(1, 30))
    status = random.choice(reservation_statuses)
    renewal = random.choice(renewal_permissions)

    # Generate a more unique reservation number using the refined method
    reservation_id = generate_refined_reservation_number(user_id, checkout_time)

    reservations.append((reservation_id, user_id, checkout_store_id, return_store_id, car_model_id, checkout_time,
                         return_time, status, renewal))

    # Generate actual orders based on the reservation
    if status in ['借出', '已还', '逾期']:
        car_id = random.choice(cars)[0]
        amount = round(random.uniform(50, 500), 2)  # Random rental amount
        actual_orders.append((reservation_id, checkout_time, checkout_store_id, return_time, return_store_id, car_id, user_id, amount))

# Generate SystemAdmins
for i in range(NUM_SYSTEM_ADMINS):
    system_admins.append((generate_random_string(), generate_random_string()))

# Generate SQL
# Generate SQL
store_sql = "INSERT INTO Store (StoreID, StoreLocation, StoreAdminID, StoreName) VALUES\n" + \
            ",\n".join([f"('{s[0]}', '{s[1]}', '{s[2]}', '{s[3]}')" for s in stores]) + ";"

user_sql = "INSERT INTO User (Username, Password, UserID, UserAddress, PhoneNumber, RegistrationDate) VALUES\n" + \
           ",\n".join([f"('{u[0]}', '{u[1]}', '{u[2]}', '{u[3]}', '{u[4]}', '{u[5]}')" for u in users]) + ";"

car_model_sql = "INSERT INTO CarModel (CarModelID, CarType, Brand, Price) VALUES\n" + \
                ",\n".join([f"('{cm[0]}', '{cm[1]}', '{cm[2]}', {cm[3]})" for cm in car_models]) + ";"

car_sql = "INSERT INTO Car (CarID, CarModelID, ProductionDate, LicensePlateNumber, CarCondition, CarStatus, StoreID) VALUES\n" + \
          ",\n".join([f"('{c[0]}', '{c[1]}', '{c[2]}', '{c[3]}', '{c[4]}', '{c[5]}', '{c[6]}')" for c in cars]) + ";"

reservation_sql = "INSERT INTO Reservation (ReservationID, UserID, ExpectedCheckoutStoreID, ExpectedReturnStoreID, CarModelID, ExpectedCheckoutTime, ExpectedReturnTime, ReservationStatus, RenewalPermission) VALUES\n" + \
                  ",\n".join([f"('{r[0]}', '{r[1]}', '{r[2]}', '{r[3]}', '{r[4]}', '{r[5]}', '{r[6]}', '{r[7]}', '{r[8]}')" for r in reservations]) + ";"

actual_order_sql = "INSERT INTO ActualOrder (ReservationOrderID, CheckoutTime, CheckoutStoreID, ReturnTime, ReturnStoreID, CarID, UserID, Amount) VALUES\n" + \
                   ",\n".join([f"('{ao[0]}', '{ao[1]}', '{ao[2]}', '{ao[3]}', '{ao[4]}', '{ao[5]}', '{ao[6]}', {ao[7]})" for ao in actual_orders]) + ";"

store_admin_sql = "INSERT INTO StoreAdmin (StoreAdminID, StoreAdminPassword) VALUES\n" + \
                  ",\n".join([f"('{sa[0]}', '{sa[1]}')" for sa in store_admins]) + ";"

system_admin_sql = "INSERT INTO SystemAdmin (SystemAdminID, SystemAdminPassword) VALUES\n" + \
                   ",\n".join([f"('{sa[0]}', '{sa[1]}')" for sa in system_admins]) + ";"

# Output SQL to file or console
with open('insert_data.sql', 'w', encoding='utf-8') as f:
    f.write(store_sql + "\n\n")
    f.write(user_sql + "\n\n")
    f.write(car_model_sql + "\n\n")
    f.write(car_sql + "\n\n")
    f.write(reservation_sql + "\n\n")
    f.write(actual_order_sql + "\n\n")  # Write actual order SQL
    f.write(store_admin_sql + "\n\n")
    f.write(system_admin_sql + "\n\n")
    f.write(create_event_sql + "\n")

print("SQL scripts generated successfully.")
disable_fk_check_sql = "SET FOREIGN_KEY_CHECKS = 0;"
enable_fk_check_sql = "SET FOREIGN_KEY_CHECKS = 1;"

# Print or save the generated SQL statements
print(store_sql)
print(user_sql)
print(car_model_sql)
print(car_sql)
print(reservation_sql)
print(actual_order_sql)  # Print actual order SQL
print(store_admin_sql)
print(system_admin_sql)

import mysql.connector

# Database connection
db = mysql.connector.connect(
    host="localhost",
    port="3307",
    user="root",
    password="1q3e5t7u9o",
    database="CarRentalDB"
)

cursor = db.cursor()

# Execute SQL
try:
    cursor.execute(disable_fk_check_sql)
    cursor.execute(store_sql)
    cursor.execute(user_sql)
    cursor.execute(car_model_sql)
    cursor.execute(car_sql)
    cursor.execute(reservation_sql)
    cursor.execute(actual_order_sql)  # Execute actual order SQL
    cursor.execute(store_admin_sql)
    cursor.execute(system_admin_sql)
    update_unrented_to_invalid()
    update_rented_to_overdue()
    update_car_status_to_rented()
    update_not_begin()
#    cursor.execute(create_event_sql)
    db.commit()
    print("Data inserted successfully")
except Exception as e:
    db.rollback()
    print(f"Error: {e}")
finally:
    # Ensure foreign key checks are re-enabled even if an error occurs
    cursor.execute(enable_fk_check_sql)
    cursor.close()
    db.close()